package o2o.web.actions.authrole;

import o2o.config.dao.UserDaoSql;
import com.jinyou.utils.common.DateUtils;
import com.jinyou.utils.common.ObjectUtils;
import com.jinyou.utils.common.ValidateUtil;
import o2o.cache.authrole.AuthMemory;
import o2o.cache.authrole.RoleAuthRelationMemory;
import o2o.cache.authrole.RoleMemory;
import o2o.cache.authrole.UserRoleAuthMemory;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.authrole.AuthInfo;
import o2o.dao.PO.authrole.RoleAuthRelationInfo;
import o2o.dao.PO.authrole.RoleInfo;
import o2o.dao.PO.authrole.UserRoleAuthInfo;
import o2o.dao.PO.common.UserType;
import o2o.dao.PO.company.SysCityCompanyInfo;
import o2o.dao.VO.authrole.AuthInfoVO;
import o2o.dao.VO.authrole.ROLE_AUTH_TYPE;
import o2o.dao.VO.authrole.RoleInfoVO;
import o2o.dao.VO.authrole.UserRoleAuthInfoVO;
import o2o.web.actions.company.SysCityCompanyMan;
import org.apache.commons.lang.StringUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Created with IntelliJ IDEA.
 *
 * @User: @Created by yangtk
 * @Date: @Date 2019/7/12 17:48
 * @Classname: UathRoleMan
 * @To change this template use File | Settings | File Templates.
 */
// 权限 和 角色操作类
public class AuthRoleMan {

    // ***************************用户创建角色相关*********************************************
    // 获取公司创建的角色信息 select
    public static List<RoleInfoVO> getCreateListOfUserRoleInfoByCompanyId(Long companyId) {
        if (companyId == null) {
            return null;
        }
        List<RoleInfo> roleInfoList = RoleMemory.listOfCompanyId(companyId);
        if (roleInfoList == null || roleInfoList.isEmpty()) {
            return null;
        }
        return toUserRoleInfoVOListConver(roleInfoList);
    }

    // 创建一个角色信息 create
    public static void createRole(String auths, String createUser, RoleInfo roleInfo, List<AuthInfoVO> authInfoVOS) throws Exception {
        if (roleInfo == null) {
            throw new Exception("角色信息不能为空");
        }
//        if (auths == null) {
//            throw new Exception("权限洗不能为空");
//        }
        if (roleInfo.getCompanyId() == null) {
            throw new Exception("公司id不能为空");
        }
        if (StringUtils.isEmpty(createUser)) {
            throw new Exception("创建者不能为空");
        }
        if (StringUtils.isEmpty(roleInfo.getRoleName())) {
            throw new Exception("权限名称不能为空");
        }

        // 数据
        Connection conn = getSqlConnetion(true);
        if (conn == null) {
            throw new Exception("数据库连接异常");
        }
        roleInfo.setDelFlag(0).setId(null).setCreateTime(DateUtils.getCurrTime());
        Long roleId = null;
        try {
            roleId = UserDaoSql.getInstance().saveObj(PojoTables.ROLE_INFO, roleInfo, conn);
        } catch (Exception e) {
            e.printStackTrace();
            UserDaoSql.getInstance().rollback(conn);
            throw new Exception(e.getMessage());
        }
        if (roleId == null) {
            UserDaoSql.getInstance().rollback(conn);
            throw new Exception("操作失败,请稍后重试");
        }
        // 有效的权限id信息
        Set<Long> validSet = checkValidAuthIds(auths, authInfoVOS);
        // 添加角色的和权限的关系
        try {
            modifyRoleAuthRelation(validSet, roleId, createUser, conn);
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            UserDaoSql.getInstance().rollback(conn);
            throw new Exception(e.getMessage());
        } finally {
            UserDaoSql.getInstance().close(conn);
        }

    }

    // 添加角色和权限之间的关系
    private static void modifyRoleAuthRelation(Set<Long> authIdSet, Long roleId, String opUser, Connection con) throws Exception {
        if (roleId == null) {
            throw new Exception("角色id不能为空");
        }
        if (con == null) {
            throw new Exception("数据库连接异常");
        }
        // 首先删除关系
        StringBuilder delSql = new StringBuilder();
        delSql.append("update ").append(PojoTables.ROLE_AUTH_RELATION).append(" set delFlag = 1,updateUser ='")
                .append(opUser).append("'").append(" where roleId =").append(roleId).append(" and  ")
                .append(" delFlag = 0 ");
        UserDaoSql.getInstance().executeBySql(delSql.toString(), con);
        // 插入新的关系
        if (authIdSet != null && !authIdSet.isEmpty()) {
            long time = DateUtils.getCurrTime();
            for (Long authId : authIdSet) {
                if (authId == null) {
                    continue;
                }
                RoleAuthRelationInfo info = new RoleAuthRelationInfo();
                info.setCreateUser(opUser).setCreateTime(time).setDelFlag(0).setAuthId(authId).setRoleId(roleId);
                UserDaoSql.getInstance().saveObj(PojoTables.ROLE_AUTH_RELATION, info, con);
            }
        }
    }

    // 删除一个角色信息 del
    public static void delUserCreateRole(Set<Long> roleIdSet, String opUser) throws Exception {
        if (roleIdSet == null || roleIdSet.isEmpty()) {
            throw new Exception("id不能为空");
        }
        //
        Connection conn = getSqlConnetion(true);
        if (conn == null) {
            throw new Exception("数据库连接异常");
        }

        StringBuilder roleSql = new StringBuilder();
        roleSql.append("update ").append(PojoTables.ROLE_INFO).append(" set delFlag = 1 ,updateUser = '").append(opUser).append("'")
                .append(" where id in (").append(StringUtils.join(roleIdSet, ",")).append(")");

        StringBuilder authRoleSql = new StringBuilder();
        authRoleSql.append("update ").append(PojoTables.ROLE_AUTH_RELATION).append(" set delFlag = 1")
                .append(" ,updateUser ='").append(opUser).append("'")
                .append(" where delFlag =0 and roleId in (").append(StringUtils.join(roleIdSet, ",")).append(")");
        try {
            UserDaoSql.getInstance().executeBySql(roleSql.toString(), conn);
            UserDaoSql.getInstance().executeBySql(authRoleSql.toString(), conn);
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            UserDaoSql.getInstance().close(conn);
        }
    }

    // 更新创建的角色信息 update
    public static void updateUserCreateRole(String auths, RoleInfo roleInfo, List<AuthInfoVO> authInfoVOS, String opUser) throws Exception {
        if (roleInfo == null || roleInfo.getId() == null) {
            throw new Exception("id不能为空");
        }
        RoleInfo roleInfoDB = RoleMemory.getById(roleInfo.getId());
        if (roleInfoDB == null) {
            throw new Exception("未获取到角色信息");
        }
        String roleNames = "";
        if (StringUtils.isNotEmpty(roleInfo.getRoleName())) {
            roleNames = ",roleName = '" + roleInfo.getRoleName() + "'";
        }
        String code = "";
        if (StringUtils.isNotEmpty(roleInfo.getCode())) {
            code = ",code = '" + roleInfo.getCode() + "'";
        }

        //
        Connection conn = getSqlConnetion(true);
        Set<Long> validSet = checkValidAuthIds(auths, authInfoVOS);
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(PojoTables.ROLE_INFO).append(" set updateUser ='").append(opUser).append("'")
                .append(roleNames).append(code)
                .append(" where delFlag = 0 and id = ").append(roleInfo.getId());

        try {
            UserDaoSql.getInstance().executeBySql(sql.toString());
            modifyRoleAuthRelation(validSet, roleInfoDB.getId(), opUser, conn);
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            UserDaoSql.getInstance().rollback(conn);
            throw new Exception(e.getMessage());
        } finally {
            UserDaoSql.getInstance().close(conn);
        }
    }

    // **********************************************utils**********************************

    // 检测指定roleId是不是指定用户所在公司下
    public static boolean checkRoleIdIsInUserCompany(Long roleId, String username) {
        if (roleId == null || StringUtils.isEmpty(username)) {
            return false;
        }
        SysCityCompanyInfo companyInfo = SysCityCompanyMan.getByCompanyUsername(username);
        if (companyInfo == null) {
            return false;
        }
        List<RoleInfo> roleInfoList = RoleMemory.listOfCompanyId(companyInfo.getId());
        if (roleInfoList == null || roleInfoList.isEmpty()) {
            return false;
        }
        for (RoleInfo roleInfo : roleInfoList) {
            if (roleInfo == null) {
                continue;
            }
            if (roleId.longValue() == roleInfo.getId().longValue()) {
                return true;
            }
        }
        return false;

    }


    // 检测有效的id是否在指定的权限内
    private static Set<Long> checkValidAuthIds(String auths, List<AuthInfoVO> ruleAuthInfoVOS) {
        Set<Long> rangeSet = new HashSet<>();
        if (ruleAuthInfoVOS != null && !ruleAuthInfoVOS.isEmpty()) {
            for (AuthInfoVO authInfoVO : ruleAuthInfoVOS) {
                rangeSet.add(authInfoVO.getId());
            }
        }
        return check(auths, rangeSet);
    }

    //    检测有效的id是否在指定的角色范围内
    private static Set<Long> checkValidRoleIds(String roles, List<RoleInfoVO> ruleRoleInfoVOS) {
        Set<Long> rangeRoleSet = new HashSet<>();
        if (ruleRoleInfoVOS != null && !ruleRoleInfoVOS.isEmpty()) {
            for (RoleInfoVO roleInfoVO : ruleRoleInfoVOS) {
                rangeRoleSet.add(roleInfoVO.getId());
            }
        }
        return check(roles, rangeRoleSet);
    }

    // 检测是否在指定范围内
    private static Set<Long> check(String snap, Set<Long> rangeSet) {
        if (rangeSet == null) {
            rangeSet = new HashSet<>();
        }
        Set<Long> validSet = new HashSet<>();
        if (!StringUtils.isEmpty(snap)) {
            String part[] = StringUtils.split(snap, ",");
            if (part.length != 0) {
                for (String tmp : part) {
                    try {
                        Long id = Long.parseLong(tmp);
                        if (id != null && rangeSet.contains(id)) {
                            validSet.add(id);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        continue;
                    }
                }
            }
        }
        return validSet;
    }


    // 转换list Role对象
    private static List<RoleInfoVO> toUserRoleInfoVOListConver(List<RoleInfo> list) {
        if (list != null && list.isEmpty()) {
            return null;
        }
        List<RoleInfoVO> resultList = new ArrayList<>();
        for (RoleInfo roleInfo : list) {
            if (roleInfo == null) {
                continue;
            }
            RoleInfoVO roleInfoVO = toUserRoleInfoVOOneConver(roleInfo);
            if (roleInfoVO == null) {
                continue;
            }
            resultList.add(roleInfoVO);
        }
        return resultList;
    }

    // 转换一个userRole对象toUserRoleInfoVo
    private static RoleInfoVO toUserRoleInfoVOOneConver(RoleInfo roleInfo) {
        if (roleInfo == null) {
            return null;
        }
        RoleInfoVO roleInfoVO = new RoleInfoVO();
        try {
            ObjectUtils.copyPropertiesExclude(roleInfo, roleInfoVO);
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        if (roleInfoVO != null) {
            List<AuthInfoVO> vos = listOfAuthInfoByRoleId(roleInfoVO.getId());
            if (vos != null && !vos.isEmpty()) {
                roleInfoVO.setAuthInfoList(vos);
            }
        }
        return roleInfoVO;
    }

    // 通过role的roleId 获取权限信息一个role对应的权限信息
    private static List<AuthInfoVO> listOfAuthInfoByRoleId(Long roleId) {

        if (roleId == null) {
            return null;
        }
        Set<Long> set = RoleAuthRelationMemory.listOfAuthIdByRoleId(roleId);
        List<AuthInfoVO> authInfoList = new ArrayList<>();
        if (set != null && !set.isEmpty()) {
            for (Long id : set) {
                AuthInfo authInfo = AuthMemory.getById(id);
                if (authInfo != null) {
                    AuthInfoVO authInfoVO = new AuthInfoVO();
                    try {
                        ObjectUtils.copyPropertiesExclude(authInfo, authInfoVO);
                    } catch (Exception e) {
                        e.printStackTrace();
                        continue;
                    }
                    if (authInfoVO != null) {
                        authInfoList.add(authInfoVO);
                    }
                }
            }
        }
        return authInfoList;
    }

    // 用户拥有的角色信息
    public static List<RoleInfoVO> listOfRoleInfoVOByUsername(String username) {
        if (StringUtils.isEmpty(username)) {
            return null;
        }
        List<UserRoleAuthInfo> userRoleAuthInfos = UserRoleAuthMemory.getByUsername(username, ROLE_AUTH_TYPE.TYPE_ROLE);
        if (userRoleAuthInfos != null && !userRoleAuthInfos.isEmpty()) {
            List<RoleInfoVO> result = new ArrayList<>();
            for (UserRoleAuthInfo info : userRoleAuthInfos) {
                if (info == null || info.getAuthRoleId() == null) {
                    continue;
                }
                RoleInfo roleInfo = RoleMemory.getById(info.getAuthRoleId());
                if (roleInfo == null) {
                    continue;
                }
                RoleInfoVO roleInfoVO = toUserRoleInfoVOOneConver(roleInfo);
                if (roleInfoVO == null) {
                    continue;
                }
                result.add(roleInfoVO);
            }
            return result;
        }
        return null;
    }

    // 获取用的权限信息
    public static List<AuthInfoVO> listOfAuthInfoByUsername(String username, int userType) {
        if (StringUtils.isEmpty(username)) {
            return null;
        }
        if (userType == UserType.SUPER_ADMIN) {
            List<AuthInfo> infoList = AuthMemory.listAll();
            if (infoList == null) {
                return null;
            }
            List<AuthInfoVO> list = new ArrayList<>();
            for (AuthInfo info : infoList) {
                if (info == null) {
                    continue;
                }
                AuthInfoVO infoVO = new AuthInfoVO();
                try {
                    ObjectUtils.copyPropertiesExclude(info, infoVO);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                list.add(infoVO);
            }
            return list;
        }

        List<UserRoleAuthInfo> userRoleAuthInfos = UserRoleAuthMemory.getByUsername(username, ROLE_AUTH_TYPE.TYPE_AUTH);
        if (userRoleAuthInfos != null && !userRoleAuthInfos.isEmpty()) {
            List<AuthInfoVO> result = new ArrayList<>();
            for (UserRoleAuthInfo info : userRoleAuthInfos) {
                if (info == null || info.getAuthRoleId() == null) {
                    continue;
                }
                AuthInfo authInfo = AuthMemory.getById(info.getAuthRoleId());
                if (authInfo == null) {
                    continue;
                }
                AuthInfoVO authInfoVO = new AuthInfoVO();
                try {
                    ObjectUtils.copyPropertiesExclude(authInfo, authInfoVO);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                result.add(authInfoVO);
            }
            return result;
        }
        return null;
    }

    // 废弃
    private static List<RoleInfoVO> listOfUserRoleInfoVOByRoles(String roles) {
        if (StringUtils.isEmpty(roles)) {
            return null;
        }
        String part[] = StringUtils.split(roles, ",");
        if (part.length == 0) {
            return null;
        }
        List<RoleInfoVO> result = new ArrayList<>();
        for (String tmp : part) {
            try {
                Long id = Long.parseLong(tmp);
                RoleInfo roleInfo = RoleMemory.getById(id);
                if (roleInfo == null) {
                    continue;
                }
                RoleInfoVO roleInfoVO = toUserRoleInfoVOOneConver(roleInfo);
                if (roleInfoVO == null) {
                    continue;
                }
                result.add(roleInfoVO);
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
        }

        return result;
    }


    // 将用户的角色和权限信息整合成统一的权限信息
    private static List<AuthInfoVO> conformUserAuthsAndUserRoles(List<RoleInfoVO> roleInfoVOS, List<AuthInfoVO> authInfoVOS) {
        List<AuthInfoVO> result = new ArrayList<>();
        // 使用 map 去重
        Map<Long, AuthInfoVO> uniqueMap = new HashMap<>();
        // 角色
        if (roleInfoVOS != null && !roleInfoVOS.isEmpty()) {
            for (RoleInfoVO roleInfoVO : roleInfoVOS) {
                if (roleInfoVO == null) {
                    continue;
                }
                List<AuthInfoVO> tempList = roleInfoVO.getAuthInfoList();
                if (tempList == null || tempList.isEmpty()) {
                    continue;
                }
                for (AuthInfoVO authInfoVO : tempList) {
                    if (authInfoVO == null) {
                        continue;
                    }
                    uniqueMap.put(authInfoVO.getId(), authInfoVO);
                }
            }
        }
        // 权限整合
        if (authInfoVOS != null && !authInfoVOS.isEmpty()) {
            for (AuthInfoVO authInfoVO : authInfoVOS) {
                if (authInfoVO == null) {
                    continue;
                }
                uniqueMap.put(authInfoVO.getId(), authInfoVO);
            }
        }
        if (!uniqueMap.isEmpty()) {
            return new ArrayList<>(uniqueMap.values());
        }
        return null;
    }


    // ***************************用户拥有的角色和权限*****************************************************

    public static UserRoleAuthInfoVO getUserRoleAuthRoleByUsername(String username, int userType) {
        return getUserRoleAuthRoleByUsername(username, null, userType);
    }

    // 通过用户的账号获用户的角色和权限信息
    public static UserRoleAuthInfoVO getUserRoleAuthRoleByUsername(String username, String authType, int userType) {
        if (StringUtils.isEmpty(username)) {
            return null;
        }
        UserRoleAuthInfoVO userRoleAuthInfoVO = new UserRoleAuthInfoVO();
        // 角色信息
        List<RoleInfoVO> roleInfoVOS = listOfRoleInfoVOByUsername(username);
        if (roleInfoVOS != null) {
            userRoleAuthInfoVO.setRoleInfoList(roleInfoVOS);
        }
        // 单独的权限细信息
        List<AuthInfoVO> authInfoVOList = null;
        authInfoVOList = listOfAuthInfoByUsername(username, userType);
        if (authInfoVOList != null) {
            userRoleAuthInfoVO.setAuthInfoList(authInfoVOList);
        }
        // 将权限和角色权限全部正好在一起
        List<AuthInfoVO> authInfoVOS2 = conformUserAuthsAndUserRoles(userRoleAuthInfoVO.getRoleInfoList(), userRoleAuthInfoVO.getAuthInfoList());
        if (authInfoVOS2 != null) {
            if (userType == UserType.ADMIN || userType == UserType.COMPANY) {
                authInfoVOS2 = authInfoVOS2.stream().filter(a -> a.getIsAdminCanOwn() == 1).collect(Collectors.toList());
            } else if (userType == UserType.SUPER_COMPANY || userType == UserType.SUPER_ADMIN) {
            }
            if (ValidateUtil.isNotNull(authType)) {
                authInfoVOS2 = authInfoVOS2.stream().filter(a -> {
                    if ("agentmanager".equals(authType)) {
                        // 区域主管的权限
                        return a.getIsAgentMangerCanOwn() == 1;
                    } else if ("admin".equals(authType)) {
                        return a.getIsAdminCanOwn() == 1;
                    } else if ("staff".equals(authType)) {
                    }
                    return true;
                }).collect(Collectors.toList());
            }
            userRoleAuthInfoVO.setAuthInfoList2(authInfoVOS2);
        }
        return userRoleAuthInfoVO;
    }
    // 用户拥有的角色信息
//    private static List<RoleInfoVO> hasListOfUserRoleInfoByUsername(String username) {
//        if (StringUtils.isEmpty(username)) {
//            return null;
//        }
//        UserRoleAuthInfo authInfo = UserRoleAuthMemory.getInstance().getByUsername(username);
//        if (authInfo == null || StringUtils.isEmpty(authInfo.getRoles())) {
//            return null;
//        }
//        return listOfUserRoleInfoVOByRoles(authInfo.getRoles());
//    }


//    // 给用户授权角色和权限 废弃
//    public static void authOrRuleToUsername(UserRoleAuthInfo info, List<AuthInfoVO> authInfoVORuleS, List<RoleInfoVO> roleInfoVORules, String opUser) throws Exception {
//        if (info == null || StringUtils.isEmpty(info.getUsername())) {
//            throw new Exception("授权账户不能为空");
//        }
//        UserRoleAuthInfo infoDB = UserRoleAuthMemory.getInstance().getByUsername(info.getUsername());
//        Set<Long> validAuthIdSet = checkValidAuthIds(info.getAuths(), authInfoVORuleS);
//        Set<Long> validRoleIdSet = checkValidRoleIds(info.getRoles(), roleInfoVORules);
//        if (infoDB == null) {
//            // 新增
//            info.setCreateTime(DateUtils.getCurrTime()).setDelFlag(0)
//                    .setRoles((validRoleIdSet == null || validRoleIdSet.isEmpty()) ? null : StringUtils.join(validRoleIdSet, ","))
//                    .setAuths((validAuthIdSet == null || validAuthIdSet.isEmpty()) ? null : StringUtils.join(validAuthIdSet, ","))
//                    .setCreateUser(info.getUsername());
//            UserDaoSql.getInstance().insertObj(PojoTables.USER_ROLE_AUTH_INFO, info);
//
//        } else {
//            // 更新
//            StringBuilder updateSql = new StringBuilder();
//            updateSql.append("update ").append(PojoTables.USER_ROLE_AUTH_INFO).append(" set ")
//                    .append(" updateUser ='").append(opUser).append("'")
//                    .append(",auths='").append((validAuthIdSet == null || validAuthIdSet.isEmpty()) ? "" : StringUtils.join(validAuthIdSet, ",")).append("'")
//                    .append(",roles ='").append((validRoleIdSet == null || validRoleIdSet.isEmpty()) ? "" : StringUtils.join(validRoleIdSet, ",")).append("'")
//                    .append(" where username = '").append(info.getUsername()).append("'").append(" and delFlag =0");
//            UserDaoSql.getInstance().executeBySql(updateSql.toString());
//        }
//        UserRoleAuthMemory.getInstance().addOrModify(info.getUsername());
//    }

    // 给用户授权角色和权限
    public static void authOrRuleToUsername(String username, String auths, String roles, List<AuthInfoVO> authInfoVORuleS, List<RoleInfoVO> roleInfoVORules, String opUser) throws Exception {
        if (StringUtils.isEmpty(username)) {
            throw new Exception("授权账户不能为空");
        }
//        UserRoleAuthInfo infoDB = UserRoleAuthMemory.getInstance().getByUsername(info.getUsername());
        Set<Long> validAuthIdSet = checkValidAuthIds(auths, authInfoVORuleS);
        Set<Long> validRoleIdSet = checkValidRoleIds(roles, roleInfoVORules);
//        if (infoDB == null) {
//            // 新增
//            info.setCreateTime(DateUtils.getCurrTime()).setDelFlag(0)
//                    .setRoles((validRoleIdSet == null || validRoleIdSet.isEmpty()) ? null : StringUtils.join(validRoleIdSet, ","))
//                    .setAuths((validAuthIdSet == null || validAuthIdSet.isEmpty()) ? null : StringUtils.join(validAuthIdSet, ","))
//                    .setCreateUser(info.getUsername());
//            UserDaoSql.getInstance().insertObj(PojoTables.USER_ROLE_AUTH_INFO, info);
//
//        } else {
//            // 更新
//            StringBuilder updateSql = new StringBuilder();
//            updateSql.append("update ").append(PojoTables.USER_ROLE_AUTH_INFO).append(" set ")
//                    .append(" updateUser ='").append(opUser).append("'")
//                    .append(",auths='").append((validAuthIdSet == null || validAuthIdSet.isEmpty()) ? "" : StringUtils.join(validAuthIdSet, ",")).append("'")
//                    .append(",roles ='").append((validRoleIdSet == null || validRoleIdSet.isEmpty()) ? "" : StringUtils.join(validRoleIdSet, ",")).append("'")
//                    .append(" where username = '").append(info.getUsername()).append("'").append(" and delFlag =0");
//            UserDaoSql.getInstance().executeBySql(updateSql.toString());
//        }
        Connection conn = getSqlConnetion(true);
        StringBuilder delSql = new StringBuilder();
        delSql.append("update ").append(PojoTables.USER_ROLE_AUTH_INFO).append(" set delFlag =1 ,updateUser ='").append(opUser).append("'")
                .append(" where delFlag =0 and username ='").append(username).append("'");
        try {
            UserDaoSql.getInstance().executeBySql(delSql.toString(), conn);
        } catch (Exception e) {
            e.printStackTrace();
            UserDaoSql.getInstance().rollback(conn);
            UserDaoSql.getInstance().close(conn);
            throw new Exception(e.getMessage());
        }
        long cTime = DateUtils.getCurrTime();
        // 插入
        UserRoleAuthInfo info = new UserRoleAuthInfo();
        info.setCreateUser(opUser).setCreateTime(cTime).setUsername(username)
                .setDelFlag(0);
        // 权限
        if (validAuthIdSet != null && !validAuthIdSet.isEmpty()) {
            for (Long authId : validAuthIdSet) {
                info.setType(ROLE_AUTH_TYPE.TYPE_AUTH).setAuthRoleId(authId);
                try {
                    UserDaoSql.getInstance().saveObj(PojoTables.USER_ROLE_AUTH_INFO, info, conn);
                } catch (Exception e) {
                    e.printStackTrace();
                    UserDaoSql.getInstance().rollback(conn);
                    UserDaoSql.getInstance().close(conn);
                    throw new Exception(e.getMessage());
                }
            }
        }
        // 角色
        if (validRoleIdSet != null && !validRoleIdSet.isEmpty()) {
            for (Long roleId : validRoleIdSet) {
                info.setType(ROLE_AUTH_TYPE.TYPE_ROLE).setAuthRoleId(roleId);
                try {
                    UserDaoSql.getInstance().saveObj(PojoTables.USER_ROLE_AUTH_INFO, info, conn);
                } catch (Exception e) {
                    e.printStackTrace();
                    UserDaoSql.getInstance().rollback(conn);
                    UserDaoSql.getInstance().close(conn);
                    throw new Exception(e.getMessage());
                }
            }
        }
        try {
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            UserDaoSql.getInstance().rollback(conn);
            throw new Exception(e.getMessage());
        } finally {
            UserDaoSql.getInstance().close(conn);
        }
    }

    // *************************数据库连接问题***********************************************
    private static Connection getSqlConnetion(boolean isOpenTransaction) {
        Connection connection = null;
        try {
            connection = UserDaoSql.getInstance().getConnection();
            if (isOpenTransaction) {
                connection.setAutoCommit(false);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}
